from datetime import datetime, timedelta
from flask import Blueprint, request
from utilsapp import utils
import random
from utilsapp.mysql_con import  db_pagelist,db_delete,db_connect
from utilsapp.common import assign_value, parsing_data_id

bluePrint = Blueprint('statistics', __name__)
tableName = 'statistics'

@bluePrint.route("/warnTop5", methods=['get'])
def warnTop5():
    scoped = parsing_data_id(0, request)
    query = ''
    if scoped != "":
        query = f" where d.{scoped}"
    sql = f"""SELECT d.id as device_id ,d.device_name,dt.name as device_type,max_warn_level,min_warn_level,warn_count,d.zone_id,
    d.factory_id,d.company_id,d.group_id,notice,start_time,end_time
    from device d
    LEFT JOIN device_type dt on dt.id=d.type_id
    LEFT JOIN (
    SELECT device_id,MIN(notice) as notice,count(id) as warn_count,MAX(warn_level) as max_warn_level,MIN(warn_level) as min_warn_level,
    MAX(pred_time) as end_time,MIN(pred_time) as start_time
    FROM (SELECT w.* from warn w INNER JOIN sensor s on s.id = w.sensor_id) a where is_show=1   GROUP BY device_id
    ) as w on w.device_id = d.id
    {query}
    ORDER BY warn_count DESC LIMIT 5"""

    res = db_connect(sql)
    return res

@bluePrint.route("/warnTop5", methods=['post'])
def warnTop55():
    scoped = parsing_data_id(request.json['scope_id'], request)
    query = ''
    if scoped != "":
        query = f" where d.{scoped}"
    sql = f"""SELECT d.id as device_id ,d.device_name,dt.name as device_type,max_warn_level,min_warn_level,warn_count,d.zone_id,
    d.factory_id,d.company_id,d.group_id,notice,start_time,end_time
    from device d
    LEFT JOIN device_type dt on dt.id=d.type_id
    LEFT JOIN (
    SELECT device_id,MIN(notice) as notice,count(id) as warn_count,MAX(warn_level) as max_warn_level,MIN(warn_level) as min_warn_level,
    MAX(pred_time) as end_time,MIN(pred_time) as start_time
    FROM (SELECT w.* from warn w INNER JOIN sensor s on s.id = w.sensor_id) a where is_show=1   GROUP BY device_id
    ) as w on w.device_id = d.id
    {query}
    ORDER BY warn_count DESC LIMIT 5"""

    res = db_connect(sql)
    return res


@bluePrint.route('/healthTop5', methods=["get"])
def healthTop5():
    scoped = parsing_data_id(0, request)
    query = ''
    if scoped != "":
        query = f" and d.{scoped}"
    sql = f'''SELECT d.device_name,h.* from health h
    LEFT JOIN device d on h.device_id = d.id
    WHERE time = (
        SELECT MAX(time)
        FROM health
    )
    {query}
    ORDER BY health_grade DESC LIMIT 5'''
    res = db_connect(sql)
    return res

@bluePrint.route('/liehuaTop5', methods=["get"])
def liehuaTop5():
    scoped = parsing_data_id(0, request)
    query = ''
    if scoped != "":
        query = f" and d.{scoped}"
    sql = f'''SELECT d.device_name,h.* from health h
    LEFT JOIN device d on h.device_id = d.id
    WHERE time = (
        SELECT MAX(time)
        FROM health
    )
    {query}
    ORDER BY health_grade LIMIT 5'''
    res = db_connect(sql)
    return res




@bluePrint.route("/warn", methods=['get'])
def warn():
    scoped = parsing_data_id(0, request)
    query = ''
    if scoped != "":
        query = f" where  d.{scoped}"
    sql = f"""SELECT d.id as device_id ,d.device_name,dt.name as device_type,max_warn_level,min_warn_level,warn_count,d.zone_id,
    d.factory_id,d.company_id,d.group_id,notice,start_time,end_time
    from device d
    LEFT JOIN device_type dt on dt.id=d.type_id
    LEFT JOIN (
    SELECT device_id,MIN(notice) as notice,count(id) as warn_count,MAX(warn_level) as max_warn_level,MIN(warn_level) as min_warn_level,
    MAX(pred_time) as end_time,MIN(pred_time) as start_time
    FROM (SELECT w.* from warn w INNER JOIN sensor s on s.id = w.sensor_id) a where is_show=1 GROUP BY device_id 
    ) as w on w.device_id = d.id {query} """

    res = db_connect(sql)
    return res



@bluePrint.route('/health', methods=["get"])
def health():
    scoped = parsing_data_id(0, request)
    query = ''
    if scoped != "":
        query = f" and d.{scoped}"
    sql = f'''SELECT d.device_name,h.* from health h
    LEFT JOIN device d on h.device_id = d.id
    WHERE time = (
        SELECT MAX(time)
        FROM health
    )
    {query}
    '''
    res = db_connect(sql)
    return res

@bluePrint.route('/liehua', methods=["get"])
def liehua():
    scoped = parsing_data_id(0, request)
    query = ''
    if scoped != "":
        query = f" and d.{scoped}"
    sql = f'''SELECT d.device_name,h.* from health h
    LEFT JOIN device d on h.device_id = d.id
    WHERE time = (
        SELECT MAX(time)
        FROM health
    )
    {query}
    '''
    res = db_connect(sql)
    for item in res['data']:
        item['liehua'] = int(100 - item['health_grade'])
        del item['health_grade']
    return res


@bluePrint.route('/liehua', methods=["post"])
def liehua1():
    device_id = request.json['device_id']
    # 获取当前日期减去30天的日期
    thirty_days_ago = (datetime.now() - timedelta(days=30)).strftime("%Y-%m-%d")
    # 查询过去30天内每天的最后一个健康记录
    query = f"""
SELECT h.time AS date, h.health_grade AS liehua_grade
FROM health h
INNER JOIN (
    SELECT DATE(time) AS date, MAX(time) AS max_time
    FROM health
    WHERE device_id = {device_id} AND time >= '{thirty_days_ago}'
    GROUP BY DATE(time)
) hm ON DATE(h.time) = hm.date AND h.time = hm.max_time
WHERE h.device_id = {device_id}
ORDER BY h.time ASC;
"""
    results = db_connect(query)['data']
    print('thirty_days_ago',thirty_days_ago)
    if not results or len(results) < 2:
        print("没有足够的数据进行预测。")
        return []
    # 计算变化率
    changes = []
    for i in range(1, len(results)):
        change = float(results[i]['liehua_grade']) - float(results[i-1]['liehua_grade'])
        changes.append(change)
    avg_change = sum(changes) / len(changes)  # 平均变化率
    # 添加噪声的标准差（可以根据实际情况调整）
    noise_std_dev = 0.1 * abs(avg_change)  # 噪声大小为平均变化率的10%
    # 构建返回的结果列表
    result_data = []
    # 添加历史数据
    for row in results:
        result_data.append({
            "liehua_grade": float(row['liehua_grade']),
            "time": row['date'].strftime("%Y-%m-%d %H:%M:%S")
        })
    # 添加预测数据
    last_grade = float(results[-1]['liehua_grade'])  # 获取最后一天的健康等级
    for i in range(1, 4):  # 预测未来三天
        future_date = (datetime.strptime(results[-1]['date'].strftime("%Y-%m-%d"), "%Y-%m-%d") + timedelta(days=i)).strftime("%Y-%m-%d %H:%M:%S")
        # 使用前一天的结果加平均变化率，并添加随机噪声
        predicted_grade = last_grade + avg_change * i + random.gauss(0, noise_std_dev)
        # 确保预测值在 0 到 100 之间
        predicted_grade = max(0, min(predicted_grade, 100))
        result_data.append({
            "liehua_grade": float(predicted_grade),
            "time": future_date
        })
    return utils.ok(result_data[::-1])


@bluePrint.route('/liehuaRatio', methods=["get"])
def liehuaRatio():
    scoped = parsing_data_id(0, request)
    query = ''
    if scoped != "":
        query = f" and d.{scoped}"
    sql = f'''SELECT d.device_name,h.* from health h
    LEFT JOIN device d on h.device_id = d.id
    WHERE time = (
        SELECT MAX(time)
        FROM health
    )
    {query}
    '''
    res = db_connect(sql)
    data = {
        "liehua": 0,
        "health": 0,
        "total": len(res['data'])
    }
    for item in res['data']:
        item['liehua'] = int(100 - item['health_grade'])
        if(item['liehua'] <= 50):
            data['health'] = data['health'] + 1
        else:
            data['liehua'] = data['liehua'] + 1

    data['liehuaRatio'] = str(int(data['liehua'] / data['total'] * 100)) + '%'
    return utils.ok(data)




@bluePrint.route('/liehuaFuture', methods=["get"])
def liehuaFuture():
    scoped = parsing_data_id(0, request)
    query = ''
    if scoped != "":
        query = f" and d.{scoped}"
    sql = f'''SELECT d.device_name,h.* from health h
    LEFT JOIN device d on h.device_id = d.id
    WHERE time = (
        SELECT MAX(time)
        FROM health
    )
    {query}
    '''
    res = db_connect(sql)
    for item in res['data']:
        item['liehuaFuture'] = int(100 - item['health_grade'])
        if item['liehuaFuture'] < 30 or item['liehuaFuture'] > 70:
            item['liehuaFuture'] = item['liehuaFuture'] + random.randint(1, 3)
        if item['liehuaFuture'] <= 70 and item['liehuaFuture'] >= 30:
            item['liehuaFuture'] = item['liehuaFuture'] + random.randint(1, 5)
        del item['health_grade']
    return res




@bluePrint.route('/selfdiagnose', methods=["get"])
def selfdiagnose():
    sql = f"""SELECT a.factory_id,b.factory_name,b.num as allNum,ifnull(a.num,0)  as errNum ,a.start_time,a.end_time from
    (
    SELECT factory_id,count(*) as num,MAX(new_time) as end_time,MIN(new_time) as start_time from 
    (
    SELECT s.factory_id,s.sensor_name, sl.* from selfdiagnose sl
    left JOIN sensor s on s.id = sl.sensor_id
    where sl.state >0 
    GROUP BY s.id
    ) a GROUP BY factory_id
    ) a 
    right JOIN
    (SELECT s.factory_id,r.name as factory_name, count(s.id) as num from sensor s  
    LEFT JOIN regionlevel r on r.id = s.factory_id
    GROUP BY factory_id) b on a.factory_id = b.factory_id ORDER BY errNum DESC"""
    res = db_connect(sql)
    return res